# coding:UTF-8
from logging import *
if __name__ != '__main__':
    import sys,datetime
    if len(sys.argv)==3:
        import re
        match1 = re.match('^(20\d\d)-(0?[1-9]|1[0-2])-(0?[1-9]|[12][0-9]|3[0-1])$',sys.argv[2])
        if match1 is not None:
            year,month,monthday=match1.group(1,2,3)
            stat_date=datetime.date(int(year),int(month),int(monthday))
        else:
            sys.exit('argument error:'+sys.argv[2])
    else:
        stat_date=datetime.date.today()-datetime.timedelta(days=1)

    stat_date_line=stat_date.isoformat()
    stat_date_number=stat_date.strftime('%Y%m%d')

    import time
    ts1=int(time.mktime(stat_date.timetuple()))
    ts2=ts1+86399

    import common.db
    bill_cnn=common.db.connect('billDB')
    bill_cursor=bill_cnn.cursor()
    delete_sql='delete from statisticdb.NewRechargeUser where Date="%s"'%(stat_date_line)
    bill_cursor.execute(delete_sql)

    sql1=[]
    for i in (0,1,2,3):
        sql1.append('''select stat_info%d.RoleID,from_unixtime(FirstPayTime),from_unixtime(RoleCreateTime)
                from dmz_dm.stat_info%d left join dmz_game.role_info%d
                on stat_info%d.RoleID=role_info%d.RoleID
                where FirstPayTime between %d and %d'''%(i,i,i,i,i,ts1,ts2))
    sql1='\n union all \n'.join(sql1)

    sql2_format='''insert into statisticdb.NewRechargeUser
            (Date,ZoneID,RoleID,AccountID,RoleName,Gender,RoleCreateTime,FirstRechargeTime,DayRecharge,DayConsumeMoney,DayConsumeScore)
            select "'''+stat_date_line+'''",ZoneID,RoleID,AccountID,RoleName,Gender,"%s","%s",DayRecharge,DayConsumeMoney,DayConsumeScore
            from
            (
                select money.ZoneID,money.RoleID,money.AccountID,money.RoleName,money.Gender,
                sum(if(money.TransID like "%%TP28%%" && money.MoneyNew>money.MoneyOld,money.MoneyNew-money.MoneyOld,0)) DayRecharge,
                sum(if(money.MoneyOld>money.MoneyNew,money.MoneyOld-money.MoneyNew,0)) DayConsumeMoney
                from dmz_bill_'''+stat_date_number+'''.money
                where money.ZoneID=%d and money.RoleID=%d and money.UpdateMeans=1 and money.ResultID=0
                group by null
            ) as tmp1 left join
            (
                select sum(score.MoneyOld-score.MoneyNew) DayConsumeScore
                from dmz_bill_'''+stat_date_number+'''.score
                where score.ZoneID=%d and score.RoleID=%d and score.UpdateMeans=1 and score.ResultID=0 and score.MoneyOld>score.MoneyNew
                group by null 
            ) as tmp2
            on true
            where DayRecharge>0'''

    zones=((1,'DMZZone1DB1'),(1,'DMZZone1DB2'),(2,'DMZZone2DB1'),(101,'DMZZone101DB1'))
    for ZoneID,db_name in zones:
        zone_cnn=common.db.connect(db_name)
        zone_cursor=zone_cnn.cursor()
        num_rows=zone_cursor.execute(sql1)
        log(INFO,db_name+':'+str(num_rows))

        row=zone_cursor.fetchone()
        while row is not None:
            RoleID,FirstRechargeTime,RoleCreateTime=row
            sql2=sql2_format%(RoleCreateTime,FirstRechargeTime,ZoneID,RoleID,ZoneID,RoleID)
            bill_cursor.execute(sql2)
            row=zone_cursor.fetchone()

        zone_cursor.close()
        zone_cnn.close()

    bill_cnn.commit()
    bill_cursor.close()
    bill_cnn.close()

